using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using Server.Tools;
using System.Xml;

namespace Server.Dao
{
	public delegate void Delegate_DataTableReach(DataTable dt);
	public delegate void Delegate_AffectedRowsReach(int rows);

	class ProcedureService
	{
		private Delegate_DataTableReach dataTableReach;
		private Delegate_AffectedRowsReach affectedRowsReach;

        private static IDictionary<String, int> procedureConfig;
        
        public ProcedureService()
        {
            if(procedureConfig == null)
            {
                procedureConfig = new Dictionary<String, int>();
                XmlDocument doc = new XmlDocument();
                doc.Load("Dao\\procedure.xml");
                foreach (XmlNode node in doc["procedures"])
                {
                    procedureConfig.Add(node.Attributes["name"].Value.ToString(), Convert.ToInt32(node.Attributes["level"].Value));
                }
            }
        }

		public void PRo_BackUp(Object[] parameters, int userLevel, Delegate_AffectedRowsReach affectedRowsReach)
		{
			if (procedureConfig["PRo_BackUp"] < userLevel) { LogService.Instance.Log.Error("userlevel is not enough to access this procedure!"); return; }
			this.affectedRowsReach = affectedRowsReach;
			PRo_BackUp((string)parameters[0], (string)parameters[1]);
		}

		private void PRo_BackUp(string Path, string Name)
		{
			SqlConnection conn = new DBConnection().GetConnection();	
			SqlCommand command = new SqlCommand();
			command.Connection = conn;
			command.CommandText = "PRo_BackUp";
			command.CommandType = CommandType.StoredProcedure;
			command.Parameters.Add("@Path", SqlDbType.NVarChar).Value = Path;
			command.Parameters.Add("@Name", SqlDbType.NVarChar).Value = Name;
			AsyncCallback ac = new AsyncCallback(PRo_BackUpCallback);
			conn.Open();
			command.BeginExecuteNonQuery(ac, command);
		}

		private void PRo_BackUpCallback(IAsyncResult ar)
		{
			SqlCommand command = (SqlCommand)ar.AsyncState;
			int affectRows = command.EndExecuteNonQuery(ar);
			affectedRowsReach(affectRows);
		}

		public void Pro_Restore(Object[] parameters, int userLevel, Delegate_AffectedRowsReach affectedRowsReach)
		{
			if (procedureConfig["Pro_Restore"] < userLevel) { LogService.Instance.Log.Error("userlevel is not enough to access this procedure!"); return; }
			this.affectedRowsReach = affectedRowsReach;
			Pro_Restore((string)parameters[0], (string)parameters[1]);
		}

		private void Pro_Restore(string Path, string FileName)
		{
			SqlConnection conn = new DBConnection().GetConnection();	
			SqlCommand command = new SqlCommand();
			command.Connection = conn;
			command.CommandText = "Pro_Restore";
			command.CommandType = CommandType.StoredProcedure;
			command.Parameters.Add("@Path", SqlDbType.NVarChar).Value = Path;
			command.Parameters.Add("@FileName", SqlDbType.NVarChar).Value = FileName;
			AsyncCallback ac = new AsyncCallback(Pro_RestoreCallback);
			conn.Open();
			command.BeginExecuteNonQuery(ac, command);
		}

		private void Pro_RestoreCallback(IAsyncResult ar)
		{
			SqlCommand command = (SqlCommand)ar.AsyncState;
			int affectRows = command.EndExecuteNonQuery(ar);
			affectedRowsReach(affectRows);
		}

		public void Pro_GetAllSubject(Object[] parameters, int userLevel, Delegate_DataTableReach dataTableReach)
		{
			if (procedureConfig["Pro_GetAllSubject"] < userLevel) { LogService.Instance.Log.Error("userlevel is not enough to access this procedure!"); return; }
			this.dataTableReach = dataTableReach;
			Pro_GetAllSubject();
		}

		private void Pro_GetAllSubject()
		{
			SqlConnection conn = new DBConnection().GetConnection();	
			SqlCommand command = new SqlCommand();
			command.Connection = conn;
			command.CommandText = "Pro_GetAllSubject";
			command.CommandType = CommandType.StoredProcedure;
			AsyncCallback ac = new AsyncCallback(Pro_GetAllSubjectCallback);
			conn.Open();
			command.BeginExecuteReader(ac, command);
		}

		private void Pro_GetAllSubjectCallback(IAsyncResult ar)
		{
			SqlCommand command = (SqlCommand)ar.AsyncState;
			SqlDataReader sDR = command.EndExecuteReader(ar);
			DataTable dt = new DataTable();
			dt.Load(sDR);
			dataTableReach(dt);
		}

		public void Pro_GetQuestionTypeBySubjectName(Object[] parameters, int userLevel, Delegate_DataTableReach dataTableReach)
		{
			if (procedureConfig["Pro_GetQuestionTypeBySubjectName"] < userLevel) { LogService.Instance.Log.Error("userlevel is not enough to access this procedure!"); return; }
			this.dataTableReach = dataTableReach;
			Pro_GetQuestionTypeBySubjectName((string)parameters[0]);
		}

		private void Pro_GetQuestionTypeBySubjectName(string subject_name)
		{
			SqlConnection conn = new DBConnection().GetConnection();	
			SqlCommand command = new SqlCommand();
			command.Connection = conn;
			command.CommandText = "Pro_GetQuestionTypeBySubjectName";
			command.CommandType = CommandType.StoredProcedure;
			command.Parameters.Add("@subject_name", SqlDbType.NVarChar).Value = subject_name;
			AsyncCallback ac = new AsyncCallback(Pro_GetQuestionTypeBySubjectNameCallback);
			conn.Open();
			command.BeginExecuteReader(ac, command);
		}

		private void Pro_GetQuestionTypeBySubjectNameCallback(IAsyncResult ar)
		{
			SqlCommand command = (SqlCommand)ar.AsyncState;
			SqlDataReader sDR = command.EndExecuteReader(ar);
			DataTable dt = new DataTable();
			dt.Load(sDR);
			dataTableReach(dt);
		}

		public void Pro_GetAllExamModel(Object[] parameters, int userLevel, Delegate_DataTableReach dataTableReach)
		{
			if (procedureConfig["Pro_GetAllExamModel"] < userLevel) { LogService.Instance.Log.Error("userlevel is not enough to access this procedure!"); return; }
			this.dataTableReach = dataTableReach;
			Pro_GetAllExamModel();
		}

		private void Pro_GetAllExamModel()
		{
			SqlConnection conn = new DBConnection().GetConnection();	
			SqlCommand command = new SqlCommand();
			command.Connection = conn;
			command.CommandText = "Pro_GetAllExamModel";
			command.CommandType = CommandType.StoredProcedure;
			AsyncCallback ac = new AsyncCallback(Pro_GetAllExamModelCallback);
			conn.Open();
			command.BeginExecuteReader(ac, command);
		}

		private void Pro_GetAllExamModelCallback(IAsyncResult ar)
		{
			SqlCommand command = (SqlCommand)ar.AsyncState;
			SqlDataReader sDR = command.EndExecuteReader(ar);
			DataTable dt = new DataTable();
			dt.Load(sDR);
			dataTableReach(dt);
		}

		public void Pro_GetExamModelByModelName(Object[] parameters, int userLevel, Delegate_DataTableReach dataTableReach)
		{
			if (procedureConfig["Pro_GetExamModelByModelName"] < userLevel) { LogService.Instance.Log.Error("userlevel is not enough to access this procedure!"); return; }
			this.dataTableReach = dataTableReach;
			Pro_GetExamModelByModelName((string)parameters[0]);
		}

		private void Pro_GetExamModelByModelName(string ModelName)
		{
			SqlConnection conn = new DBConnection().GetConnection();	
			SqlCommand command = new SqlCommand();
			command.Connection = conn;
			command.CommandText = "Pro_GetExamModelByModelName";
			command.CommandType = CommandType.StoredProcedure;
			command.Parameters.Add("@ModelName", SqlDbType.NVarChar).Value = ModelName;
			AsyncCallback ac = new AsyncCallback(Pro_GetExamModelByModelNameCallback);
			conn.Open();
			command.BeginExecuteReader(ac, command);
		}

		private void Pro_GetExamModelByModelNameCallback(IAsyncResult ar)
		{
			SqlCommand command = (SqlCommand)ar.AsyncState;
			SqlDataReader sDR = command.EndExecuteReader(ar);
			DataTable dt = new DataTable();
			dt.Load(sDR);
			dataTableReach(dt);
		}

		public void Pro_AddSubject(Object[] parameters, int userLevel, Delegate_AffectedRowsReach affectedRowsReach)
		{
			if (procedureConfig["Pro_AddSubject"] < userLevel) { LogService.Instance.Log.Error("userlevel is not enough to access this procedure!"); return; }
			this.affectedRowsReach = affectedRowsReach;
			Pro_AddSubject((string)parameters[0], (string)parameters[1]);
		}

		private void Pro_AddSubject(string SubjectName, string UserName)
		{
			SqlConnection conn = new DBConnection().GetConnection();	
			SqlCommand command = new SqlCommand();
			command.Connection = conn;
			command.CommandText = "Pro_AddSubject";
			command.CommandType = CommandType.StoredProcedure;
			command.Parameters.Add("@SubjectName", SqlDbType.NVarChar).Value = SubjectName;
			command.Parameters.Add("@UserName", SqlDbType.VarChar).Value = UserName;
			AsyncCallback ac = new AsyncCallback(Pro_AddSubjectCallback);
			conn.Open();
			command.BeginExecuteNonQuery(ac, command);
		}

		private void Pro_AddSubjectCallback(IAsyncResult ar)
		{
			SqlCommand command = (SqlCommand)ar.AsyncState;
			int affectRows = command.EndExecuteNonQuery(ar);
			affectedRowsReach(affectRows);
		}

		public void Pro_DelSubject(Object[] parameters, int userLevel, Delegate_AffectedRowsReach affectedRowsReach)
		{
			if (procedureConfig["Pro_DelSubject"] < userLevel) { LogService.Instance.Log.Error("userlevel is not enough to access this procedure!"); return; }
			this.affectedRowsReach = affectedRowsReach;
			Pro_DelSubject((string)parameters[0]);
		}

		private void Pro_DelSubject(string SubjectName)
		{
			SqlConnection conn = new DBConnection().GetConnection();	
			SqlCommand command = new SqlCommand();
			command.Connection = conn;
			command.CommandText = "Pro_DelSubject";
			command.CommandType = CommandType.StoredProcedure;
			command.Parameters.Add("@SubjectName", SqlDbType.NVarChar).Value = SubjectName;
			AsyncCallback ac = new AsyncCallback(Pro_DelSubjectCallback);
			conn.Open();
			command.BeginExecuteNonQuery(ac, command);
		}

		private void Pro_DelSubjectCallback(IAsyncResult ar)
		{
			SqlCommand command = (SqlCommand)ar.AsyncState;
			int affectRows = command.EndExecuteNonQuery(ar);
			affectedRowsReach(affectRows);
		}

		public void Pro_UpdateSubject(Object[] parameters, int userLevel, Delegate_AffectedRowsReach affectedRowsReach)
		{
			if (procedureConfig["Pro_UpdateSubject"] < userLevel) { LogService.Instance.Log.Error("userlevel is not enough to access this procedure!"); return; }
			this.affectedRowsReach = affectedRowsReach;
			Pro_UpdateSubject((string)parameters[0], (string)parameters[1]);
		}

		private void Pro_UpdateSubject(string SubjectName, string NewName)
		{
			SqlConnection conn = new DBConnection().GetConnection();	
			SqlCommand command = new SqlCommand();
			command.Connection = conn;
			command.CommandText = "Pro_UpdateSubject";
			command.CommandType = CommandType.StoredProcedure;
			command.Parameters.Add("@SubjectName", SqlDbType.NVarChar).Value = SubjectName;
			command.Parameters.Add("@NewName", SqlDbType.NVarChar).Value = NewName;
			AsyncCallback ac = new AsyncCallback(Pro_UpdateSubjectCallback);
			conn.Open();
			command.BeginExecuteNonQuery(ac, command);
		}

		private void Pro_UpdateSubjectCallback(IAsyncResult ar)
		{
			SqlCommand command = (SqlCommand)ar.AsyncState;
			int affectRows = command.EndExecuteNonQuery(ar);
			affectedRowsReach(affectRows);
		}

		public void Pro_UpdateUser(Object[] parameters, int userLevel, Delegate_AffectedRowsReach affectedRowsReach)
		{
			if (procedureConfig["Pro_UpdateUser"] < userLevel) { LogService.Instance.Log.Error("userlevel is not enough to access this procedure!"); return; }
			this.affectedRowsReach = affectedRowsReach;
			Pro_UpdateUser((string)parameters[0], (string)parameters[1], (int)parameters[2], (string)parameters[3], (string)parameters[4]);
		}

		private void Pro_UpdateUser(string UserName, string RealName, int UserLevel, string UserPassword, string NewUserName)
		{
			SqlConnection conn = new DBConnection().GetConnection();	
			SqlCommand command = new SqlCommand();
			command.Connection = conn;
			command.CommandText = "Pro_UpdateUser";
			command.CommandType = CommandType.StoredProcedure;
			command.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = UserName;
			command.Parameters.Add("@RealName", SqlDbType.NVarChar).Value = RealName;
			command.Parameters.Add("@UserLevel", SqlDbType.Int).Value = UserLevel;
			command.Parameters.Add("@UserPassword", SqlDbType.VarChar).Value = UserPassword;
			command.Parameters.Add("@NewUserName", SqlDbType.VarChar).Value = NewUserName;
			AsyncCallback ac = new AsyncCallback(Pro_UpdateUserCallback);
			conn.Open();
			command.BeginExecuteNonQuery(ac, command);
		}

		private void Pro_UpdateUserCallback(IAsyncResult ar)
		{
			SqlCommand command = (SqlCommand)ar.AsyncState;
			int affectRows = command.EndExecuteNonQuery(ar);
			affectedRowsReach(affectRows);
		}

		public void Pro_GetQuestionTypeTotalNumByQuestionType(Object[] parameters, int userLevel, Delegate_DataTableReach dataTableReach)
		{
			if (procedureConfig["Pro_GetQuestionTypeTotalNumByQuestionType"] < userLevel) { LogService.Instance.Log.Error("userlevel is not enough to access this procedure!"); return; }
			this.dataTableReach = dataTableReach;
			Pro_GetQuestionTypeTotalNumByQuestionType((string)parameters[0], (string)parameters[1]);
		}

		private void Pro_GetQuestionTypeTotalNumByQuestionType(string QuestionType, string SubjectName)
		{
			SqlConnection conn = new DBConnection().GetConnection();	
			SqlCommand command = new SqlCommand();
			command.Connection = conn;
			command.CommandText = "Pro_GetQuestionTypeTotalNumByQuestionType";
			command.CommandType = CommandType.StoredProcedure;
			command.Parameters.Add("@QuestionType", SqlDbType.NVarChar).Value = QuestionType;
			command.Parameters.Add("@SubjectName", SqlDbType.NVarChar).Value = SubjectName;
			AsyncCallback ac = new AsyncCallback(Pro_GetQuestionTypeTotalNumByQuestionTypeCallback);
			conn.Open();
			command.BeginExecuteReader(ac, command);
		}

		private void Pro_GetQuestionTypeTotalNumByQuestionTypeCallback(IAsyncResult ar)
		{
			SqlCommand command = (SqlCommand)ar.AsyncState;
			SqlDataReader sDR = command.EndExecuteReader(ar);
			DataTable dt = new DataTable();
			dt.Load(sDR);
			dataTableReach(dt);
		}

		public void Pro_GetUserByName(Object[] parameters, int userLevel, Delegate_DataTableReach dataTableReach)
		{
			if (procedureConfig["Pro_GetUserByName"] < userLevel) { LogService.Instance.Log.Error("userlevel is not enough to access this procedure!"); return; }
			this.dataTableReach = dataTableReach;
			Pro_GetUserByName((string)parameters[0]);
		}

		private void Pro_GetUserByName(string UserName)
		{
			SqlConnection conn = new DBConnection().GetConnection();	
			SqlCommand command = new SqlCommand();
			command.Connection = conn;
			command.CommandText = "Pro_GetUserByName";
			command.CommandType = CommandType.StoredProcedure;
			command.Parameters.Add("@UserName", SqlDbType.VarChar).Value = UserName;
			AsyncCallback ac = new AsyncCallback(Pro_GetUserByNameCallback);
			conn.Open();
			command.BeginExecuteReader(ac, command);
		}

		private void Pro_GetUserByNameCallback(IAsyncResult ar)
		{
			SqlCommand command = (SqlCommand)ar.AsyncState;
			SqlDataReader sDR = command.EndExecuteReader(ar);
			DataTable dt = new DataTable();
			dt.Load(sDR);
			dataTableReach(dt);
		}

		public void Pro_AddUser(Object[] parameters, int userLevel, Delegate_AffectedRowsReach affectedRowsReach)
		{
			if (procedureConfig["Pro_AddUser"] < userLevel) { LogService.Instance.Log.Error("userlevel is not enough to access this procedure!"); return; }
			this.affectedRowsReach = affectedRowsReach;
			Pro_AddUser((string)parameters[0], (string)parameters[1], (int)parameters[2], (string)parameters[3]);
		}

		private void Pro_AddUser(string username, string userpassword, int UserLevel, string userrealname)
		{
			SqlConnection conn = new DBConnection().GetConnection();	
			SqlCommand command = new SqlCommand();
			command.Connection = conn;
			command.CommandText = "Pro_AddUser";
			command.CommandType = CommandType.StoredProcedure;
			command.Parameters.Add("@username", SqlDbType.VarChar).Value = username;
			command.Parameters.Add("@userpassword", SqlDbType.VarChar).Value = userpassword;
			command.Parameters.Add("@UserLevel", SqlDbType.Int).Value = UserLevel;
			command.Parameters.Add("@userrealname", SqlDbType.NVarChar).Value = userrealname;
			AsyncCallback ac = new AsyncCallback(Pro_AddUserCallback);
			conn.Open();
			command.BeginExecuteNonQuery(ac, command);
		}

		private void Pro_AddUserCallback(IAsyncResult ar)
		{
			SqlCommand command = (SqlCommand)ar.AsyncState;
			int affectRows = command.EndExecuteNonQuery(ar);
			affectedRowsReach(affectRows);
		}

		public void Pro_DelUser(Object[] parameters, int userLevel, Delegate_AffectedRowsReach affectedRowsReach)
		{
			if (procedureConfig["Pro_DelUser"] < userLevel) { LogService.Instance.Log.Error("userlevel is not enough to access this procedure!"); return; }
			this.affectedRowsReach = affectedRowsReach;
			Pro_DelUser((string)parameters[0]);
		}

		private void Pro_DelUser(string username)
		{
			SqlConnection conn = new DBConnection().GetConnection();	
			SqlCommand command = new SqlCommand();
			command.Connection = conn;
			command.CommandText = "Pro_DelUser";
			command.CommandType = CommandType.StoredProcedure;
			command.Parameters.Add("@username", SqlDbType.VarChar).Value = username;
			AsyncCallback ac = new AsyncCallback(Pro_DelUserCallback);
			conn.Open();
			command.BeginExecuteNonQuery(ac, command);
		}

		private void Pro_DelUserCallback(IAsyncResult ar)
		{
			SqlCommand command = (SqlCommand)ar.AsyncState;
			int affectRows = command.EndExecuteNonQuery(ar);
			affectedRowsReach(affectRows);
		}

		public void Pro_GetQuestionsRequire(Object[] parameters, int userLevel, Delegate_DataTableReach dataTableReach)
		{
			if (procedureConfig["Pro_GetQuestionsRequire"] < userLevel) { LogService.Instance.Log.Error("userlevel is not enough to access this procedure!"); return; }
			this.dataTableReach = dataTableReach;
			Pro_GetQuestionsRequire((int)parameters[0]);
		}

		private void Pro_GetQuestionsRequire(int id)
		{
			SqlConnection conn = new DBConnection().GetConnection();	
			SqlCommand command = new SqlCommand();
			command.Connection = conn;
			command.CommandText = "Pro_GetQuestionsRequire";
			command.CommandType = CommandType.StoredProcedure;
			command.Parameters.Add("@id", SqlDbType.Int).Value = id;
			AsyncCallback ac = new AsyncCallback(Pro_GetQuestionsRequireCallback);
			conn.Open();
			command.BeginExecuteReader(ac, command);
		}

		private void Pro_GetQuestionsRequireCallback(IAsyncResult ar)
		{
			SqlCommand command = (SqlCommand)ar.AsyncState;
			SqlDataReader sDR = command.EndExecuteReader(ar);
			DataTable dt = new DataTable();
			dt.Load(sDR);
			dataTableReach(dt);
		}

		public void Pro_GetUser(Object[] parameters, int userLevel, Delegate_DataTableReach dataTableReach)
		{
			if (procedureConfig["Pro_GetUser"] < userLevel) { LogService.Instance.Log.Error("userlevel is not enough to access this procedure!"); return; }
			this.dataTableReach = dataTableReach;
			Pro_GetUser();
		}

		private void Pro_GetUser()
		{
			SqlConnection conn = new DBConnection().GetConnection();	
			SqlCommand command = new SqlCommand();
			command.Connection = conn;
			command.CommandText = "Pro_GetUser";
			command.CommandType = CommandType.StoredProcedure;
			AsyncCallback ac = new AsyncCallback(Pro_GetUserCallback);
			conn.Open();
			command.BeginExecuteReader(ac, command);
		}

		private void Pro_GetUserCallback(IAsyncResult ar)
		{
			SqlCommand command = (SqlCommand)ar.AsyncState;
			SqlDataReader sDR = command.EndExecuteReader(ar);
			DataTable dt = new DataTable();
			dt.Load(sDR);
			dataTableReach(dt);
		}

		public void Pro_ChangePassword(Object[] parameters, int userLevel, Delegate_AffectedRowsReach affectedRowsReach)
		{
			if (procedureConfig["Pro_ChangePassword"] < userLevel) { LogService.Instance.Log.Error("userlevel is not enough to access this procedure!"); return; }
			this.affectedRowsReach = affectedRowsReach;
			Pro_ChangePassword((string)parameters[0], (string)parameters[1], (string)parameters[2]);
		}

		private void Pro_ChangePassword(string UserName, string OldPassword, string NewPassword)
		{
			SqlConnection conn = new DBConnection().GetConnection();	
			SqlCommand command = new SqlCommand();
			command.Connection = conn;
			command.CommandText = "Pro_ChangePassword";
			command.CommandType = CommandType.StoredProcedure;
			command.Parameters.Add("@UserName", SqlDbType.VarChar).Value = UserName;
			command.Parameters.Add("@OldPassword", SqlDbType.VarChar).Value = OldPassword;
			command.Parameters.Add("@NewPassword", SqlDbType.VarChar).Value = NewPassword;
			AsyncCallback ac = new AsyncCallback(Pro_ChangePasswordCallback);
			conn.Open();
			command.BeginExecuteNonQuery(ac, command);
		}

		private void Pro_ChangePasswordCallback(IAsyncResult ar)
		{
			SqlCommand command = (SqlCommand)ar.AsyncState;
			int affectRows = command.EndExecuteNonQuery(ar);
			affectedRowsReach(affectRows);
		}

		public void Pro_GetUserLogin(Object[] parameters, int userLevel, Delegate_DataTableReach dataTableReach)
		{
			if (procedureConfig["Pro_GetUserLogin"] < userLevel) { LogService.Instance.Log.Error("userlevel is not enough to access this procedure!"); return; }
			this.dataTableReach = dataTableReach;
			Pro_GetUserLogin((string)parameters[0], (string)parameters[1]);
		}

		private void Pro_GetUserLogin(string UserName, string UserPassword)
		{
			SqlConnection conn = new DBConnection().GetConnection();	
			SqlCommand command = new SqlCommand();
			command.Connection = conn;
			command.CommandText = "Pro_GetUserLogin";
			command.CommandType = CommandType.StoredProcedure;
			command.Parameters.Add("@UserName", SqlDbType.VarChar).Value = UserName;
			command.Parameters.Add("@UserPassword", SqlDbType.VarChar).Value = UserPassword;
			AsyncCallback ac = new AsyncCallback(Pro_GetUserLoginCallback);
			conn.Open();
			command.BeginExecuteReader(ac, command);
		}

		private void Pro_GetUserLoginCallback(IAsyncResult ar)
		{
			SqlCommand command = (SqlCommand)ar.AsyncState;
			SqlDataReader sDR = command.EndExecuteReader(ar);
			DataTable dt = new DataTable();
			dt.Load(sDR);
			dataTableReach(dt);
		}


		
	}
}